package com.spi.soconversion.orders;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Iterator;

import com.spi.soconversion.base.SOCException;
import com.spi.soconversion.util.DBConnection;
import com.spi.soconversion.util.ErrorConstants;
import com.spi.soconversion.util.ErrorMessageReader;
import com.spi.soconversion.util.QueryConstants;
import com.spi.soconversion.util.QueryReader;
import com.spi.soconversion.util.SOCConstants;
import com.spi.soconversion.util.SOCFileWriter;
import com.spi.soconversion.util.SOCUtility;

public class SOCFetchClosedSalesOrder {
	private static final String POS_STORE_NUM = "POS_STORE_NUM";
	private Connection d_connection = null;
	private String d_logFileName = null;
	private SOCFileWriter d_fileWriter = null;
	private CallableStatement cs = null;
	private Statement stmt = null;
	
	public SOCFetchClosedSalesOrder(String logFileName, SOCFileWriter fileWriter)
	{
		d_logFileName = logFileName;
		d_fileWriter = fileWriter;
	}
	
	public void fetchClosedSalesOrder() throws SOCException
	{
		d_connection = DBConnection.getConnection();
		
		populateClosedOrderIn13Months();
		populateClosedOrderBfr13Months();		
		populateClosedOrderLinesIn13Months();
		populateClosedOrderLinesBfr13Months();		
		analyzeClosedOrderLines();
		populateClosedOrdersPOSStoreNumber();
		populateClosedOrdersPOSProvince();
	}
	
	private void populateClosedOrderIn13Months() throws SOCException
	{
		String closedOrder13MonthOldQuery = QueryReader.getQuery(QueryConstants.GET_CLOSED_ORDERS_13_MONTH_OLD_QUERY);
        	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_ORDERS_IN_13MONTHS_STARTS));
    		cs = d_connection.prepareCall(closedOrder13MonthOldQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_CLOSED_ORDERS_IN_13MONTHS)));
				throw new SOCException(
        		ErrorMessageReader.getError(ErrorConstants.NO_RECORD_POPULATION_TBL_CLOSED_ORDERS_IN_13MONTHS));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_ORDERS_IN_13MONTHS_ENDS ));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_CLOSED_ORDERS_IN_13MONTHS)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_CLOSED_ORDERS_IN_13MONTHS),se);
		}		
		
		analyzeClosedOrderIn13Months();
	}
		
	private void analyzeClosedOrderIn13Months() throws SOCException
	{
		String closedOrderAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_CLOSED_ORDERS_13_MONTH_OLD_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_TBL_CLOSED_ORDERS_IN_13MONTHS_STARTS));
    		stmt = d_connection.createStatement();
    	    stmt.execute(closedOrderAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_TBL_CLOSED_ORDERS_IN_13MONTHS_ENDS));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_CLOSED_ORDERS_IN_13MONTHS)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_CLOSED_ORDERS_IN_13MONTHS),se);
		}
	}
	
	private void populateClosedOrderBfr13Months() throws SOCException
	{
		String closedOrderBfr13MonthOldQuery = QueryReader.getQuery(QueryConstants.GET_CLOSED_ORDERS_BFR_13_MONTH_OLD_QUERY);
        	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_ORDERS_BFR_13MONTHS_STARTS));
    		cs = d_connection.prepareCall(closedOrderBfr13MonthOldQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_CLOSED_ORDERS_BFR_13MONTHS)));
				throw new SOCException(
        		ErrorMessageReader.getError(ErrorConstants.NO_RECORD_POPULATION_TBL_CLOSED_ORDERS_BFR_13MONTHS));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_ORDERS_BFR_13MONTHS_ENDS ));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_CLOSED_ORDERS_BFR_13MONTHS)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_CLOSED_ORDERS_BFR_13MONTHS),se);
		}		
		
		analyzeClosedOrderBfr13Months();
	}
		
	private void analyzeClosedOrderBfr13Months() throws SOCException
	{
		String closedOrderAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_CLOSED_ORDERS_BFR_13_MONTH_OLD_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_TBL_CLOSED_ORDERS_BFR_13MONTHS_STARTS));
    		stmt = d_connection.createStatement();
    	    stmt.execute(closedOrderAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_TBL_CLOSED_ORDERS_BFR_13MONTHS_ENDS));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_CLOSED_ORDERS_BFR_13MONTHS)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_CLOSED_ORDERS_BFR_13MONTHS),se);
		}
	}
	
	private void populateClosedOrderLinesIn13Months() throws SOCException
	{
		String closedOrderLines13MonthsQuery = QueryReader.getQuery(QueryConstants.GET_CLOSED_ORDERS_LINES_13_MONTHS_QUERY);
        	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_13_MONTHS_STARTS));
    		cs = d_connection.prepareCall(closedOrderLines13MonthsQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_IN_13MONTHS)));
				throw new SOCException(
        		ErrorMessageReader.getError(ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_IN_13MONTHS));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_13_MONTHS_ENDS ));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_IN_13MONTHS)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_IN_13MONTHS),se);
		}	
	}
	
	private void populateClosedOrderLinesBfr13Months() throws SOCException
	{
		String closedOrderLinesBfr13MonthsQuery = QueryReader.getQuery(QueryConstants.GET_CLOSED_ORDERS_LINES_BFR_13_MONTHS_QUERY);
        	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_BFR_13_MONTHS_STARTS));
    		cs = d_connection.prepareCall(closedOrderLinesBfr13MonthsQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_BFR_13MONTHS)));
				throw new SOCException(
        		ErrorMessageReader.getError(ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_BFR_13MONTHS));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_BFR_13_MONTHS_ENDS ));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_BFR_13MONTHS)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_CLOSED_ORDER_LINES_BFR_13MONTHS),se);
		}	
	}
		
	private void analyzeClosedOrderLines() throws SOCException
	{
		String closedOrderLinesAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_CLOSED_ORDERS_LINES_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_TBL_STAGING_CLOSED_ORDER_LINES_STARTS));
    		stmt = d_connection.createStatement();
    	    stmt.execute(closedOrderLinesAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_TBL_STAGING_CLOSED_ORDER_LINES_ENDS));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_CLOSED_ORDER_LINES)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_CLOSED_ORDER_LINES),se);
		}
	}
	
	private void populateClosedOrdersPOSStoreNumber() throws SOCException
	{
		String closedOrderLinesPOSStoreQuery = QueryReader.getQuery(QueryConstants.SELECT_DISTINCT_POS_STORE_QUERY);
    	
		Statement stmt=null;
    	ResultSet rsInfo=null;
    	ArrayList<String> posStoreNumList = new 	ArrayList<String>();
    	NumberFormat storeFormatter = new DecimalFormat("0000");
    	d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_LINES_POS_PROVINCE_POS_STARTS));
    	try
		{
    		stmt=DBConnection.getConnection().createStatement();
    		rsInfo=stmt.executeQuery(closedOrderLinesPOSStoreQuery);
			if(rsInfo!=null)
			{
				while(rsInfo.next())
				{
					String storeNum = rsInfo.getString(POS_STORE_NUM);
					posStoreNumList.add(storeFormatter.format(Integer.parseInt(storeNum))); 
				}
				
			}			
		}
		catch(SQLException sqle)
		{
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_FETCH_POS_STORE_NUM)+SOCConstants.NEW_LINE+sqle.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_FETCH_POS_STORE_NUM) ,sqle);
    		
		}	
		
		String closedOrderLinesPOSStoreFormattedQuery = QueryReader.getQuery(QueryConstants.INSERT_DISTINCT_POS_STORE_QUERY);
		
		PreparedStatement posStoreNumStmt = null;
		try {
			DBConnection.getConnection().setAutoCommit(false);
			posStoreNumStmt = DBConnection.getConnection().prepareStatement(closedOrderLinesPOSStoreFormattedQuery);
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
					ErrorConstants.FAILED_CREATE_STMT_TBL_CLOSED_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
					ErrorMessageReader.getError(
							ErrorConstants.FAILED_CREATE_STMT_TBL_CLOSED_LINES_POS_PROVINCE),se);
		}	   
	   
		Iterator<String> iter=posStoreNumList.iterator();
		while(iter.hasNext())
    	{	
			String  posStoreNum = iter.next();
			try {
				posStoreNumStmt.setString(1,posStoreNum);
				posStoreNumStmt.addBatch();
			} catch (SQLException se) {
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
						ErrorConstants.FAILED_ADDING_BATCH_TBL_CLOSED_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+se.getMessage()));
				throw new SOCException(
						ErrorMessageReader.getError(
								ErrorConstants.FAILED_ADDING_BATCH_TBL_CLOSED_LINES_POS_PROVINCE),se);
			}
			
    	}
		
		   try {
				posStoreNumStmt.executeBatch();
				DBConnection.getConnection().commit();
				DBConnection.getConnection().setAutoCommit(true);
				 d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_LINES_POS_PROVINCE_POS_ENDS));
			} catch (SQLException se) {
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
						ErrorConstants.FAILED_UPDATE_BATCH_TBL_CLOSED_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+se.getMessage()));
				throw new SOCException(
						ErrorMessageReader.getError(
								ErrorConstants.FAILED_UPDATE_BATCH_TBL_CLOSED_LINES_POS_PROVINCE),se);
			}
		
	}
	
	private void populateClosedOrdersPOSProvince() throws SOCException
	{
		String closedOrderLinesPOSProvinceQuery = QueryReader.getQuery(QueryConstants.INSERT_POS_PROVINCE_QUERY);
    	
		Statement stmt=null;
	 	d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_LINES_POS_PROVINCE_POS_PROVINCE_STARTS));
		try
		{
    		stmt=DBConnection.getConnection().createStatement();
    		stmt.executeUpdate(closedOrderLinesPOSProvinceQuery);		
		}
		catch(SQLException sqle)
		{
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_UPDATE_TBL_CLOSED_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+sqle.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_UPDATE_TBL_CLOSED_LINES_POS_PROVINCE) ,sqle);
    		
		}	
	 	d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_CLOSED_LINES_POS_PROVINCE_POS_PROVINCE_ENDS));
	}
		
}
